﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Collections;

namespace CashierSystem.SearchAccountManager
{
    class SearchAccountManager : DbConnection
    {
        public static void loadAccount(ListView lv1, string departId, string startDate, string endDate)
        {
            string sql = "SELECT t_customer.customerName,t_items.itemName, t_items.price, t_sensing.discount, t_sensing.actualPrice,t_user.name,t_sensing.sensingDate ";
            sql += "FROM t_items INNER JOIN t_sensing ON t_sensing.itemId = t_items.itemId INNER JOIN t_departItems ON t_departItems.itemId = t_items.itemId INNER JOIN ";
            sql += " t_user ON t_sensing.cashierId = t_user.userId INNER JOIN t_customer ON t_customer.customerId = t_sensing.customerId WHERE (1 = 1)";
            if (departId != "")
            {
                sql += " and t_departItems.departId =  '" + departId + "'";
            }
            if (!startDate.Equals(""))
            {
                sql += " and t_sensing.sensingDate >= convert(VARCHAR(10),'" + startDate + "')";
            }
            if (!endDate.Equals(""))
            {
                sql += " and t_sensing.sensingDate <= convert(VARCHAR(10),'" + endDate + "')";
            }

            ColumnHeader[] ch = new ColumnHeader[7];
            ch[0] = new ColumnHeader("客户姓名", 120, HorizontalAlignment.Left);
            ch[1] = new ColumnHeader("项目名称", 120, HorizontalAlignment.Left);
            ch[2] = new ColumnHeader("价格", 120, HorizontalAlignment.Left);
            ch[3] = new ColumnHeader("折扣", 120, HorizontalAlignment.Left);
            ch[4] = new ColumnHeader("实际收费", 120, HorizontalAlignment.Left);
            ch[5] = new ColumnHeader("收银员", 120, HorizontalAlignment.Left);
            ch[6] = new ColumnHeader("收费日期", 120, HorizontalAlignment.Left);
            Utils.dbReaderFillListView(lv1, ch, sql, conn);

        }
        public static void loadAccount1(ListView lv, string departId, string startDate, string endDate)
        {

            string sql = "SELECT t_customer.customerName,t_items.itemName, t_items.price, t_sensing.discount, t_sensing.actualPrice,t_user.name,t_sensing.sensingDate,";
            sql += "doctorId,doctorId1,doctorId2,doctorId3,doctorId4,doctorId5,expertFee ";
            sql += "FROM t_items INNER JOIN t_sensing ON t_sensing.itemId = t_items.itemId INNER JOIN t_departItems ON t_departItems.itemId = t_items.itemId INNER JOIN ";
            sql += " t_user ON t_sensing.cashierId = t_user.userId INNER JOIN t_customer ON t_customer.customerId = t_sensing.customerId WHERE (1 = 1)";
            if (departId != "")
            {
                sql += " and t_departItems.departId =  '" + departId + "'";
            }
            if (!startDate.Equals(""))
            {
                sql += " and t_sensing.sensingDate >= '" + startDate + "'";//convert(VARCHAR(10),
            }
            if (!endDate.Equals(""))
            {
                sql += " and t_sensing.sensingDate <= '" + endDate + "'";
            }
            sql += "order by sensingDate desc";

            lv.Items.Clear();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            double totalPrice = 0;
            double totalExpertFee = 0;
            ListViewItem li1 = new ListViewItem();
            try
            {
                conn.Close();
                conn.Open();
                OleDbDataReader dbReader = cmd.ExecuteReader();
                while (dbReader.Read())
                {
                    ListViewItem li = new ListViewItem();
                    li.SubItems.Clear();
                    li.SubItems[0].Text = dbReader["customerName"].ToString();
                    li.SubItems.Add(dbReader["itemName"].ToString());
                    li.SubItems.Add(dbReader["price"].ToString() + "元");
                    li.SubItems.Add(dbReader["discount"].ToString());
                    li.SubItems.Add(dbReader["actualPrice"].ToString() + "元");
                    li.SubItems.Add(dbReader["name"].ToString());
                    li.SubItems.Add(dbReader["sensingDate"].ToString().Substring(0,9));
                    li.SubItems.Add(dbReader["doctorId"].ToString());
                    li.SubItems.Add(dbReader["doctorId1"].ToString());
                    li.SubItems.Add(dbReader["doctorId2"].ToString());
                    li.SubItems.Add(dbReader["doctorId3"].ToString());
                    li.SubItems.Add(dbReader["doctorId4"].ToString());
                    li.SubItems.Add(dbReader["doctorId5"].ToString());
                    li.SubItems.Add(dbReader["expertFee"].ToString());
                    totalPrice += double.Parse(dbReader["actualPrice"].ToString());
                    totalExpertFee += double.Parse(dbReader["expertFee"].ToString());
                    lv.Items.Add(li);
                }
                li1.SubItems.Clear();
                li1.SubItems[0].Text = "合计";
                li1.SubItems.Add("");
                li1.SubItems.Add("");
                li1.SubItems.Add("");
                li1.SubItems.Add(totalPrice.ToString() + "元");
                li1.SubItems.Add("");
                li1.SubItems.Add("");
                li1.SubItems.Add("");
                li1.SubItems.Add("");
                li1.SubItems.Add("");
                li1.SubItems.Add("");
                li1.SubItems.Add("");
                li1.SubItems.Add("");
                li1.SubItems.Add(totalExpertFee.ToString() + "元");
                lv.Items.Add(li1);
                conn.Close();
            }
            catch (OleDbException ex)
            {
                string s = ex.ToString();
            }

            totalPrice = 0;

            sql = "SELECT t_customer.customerName, t_regist.regPrice, t_regist.regDate, t_user.name FROM t_regist INNER JOIN t_user ON t_user.userId = t_regist.cashier INNER JOIN ";
            sql += " t_customer ON t_customer.customerId = t_regist.customerId where 1=1";

            if (!startDate.Equals(""))
            {
                sql += " and t_regist.regDate >= '" + startDate + "'";
            }
            if (!endDate.Equals(""))
            {
                sql += " and t_regist.regDate <= '" + endDate + "'";
            }
            OleDbCommand cmd1 = new OleDbCommand(sql, conn);
            ListViewItem li2 = new ListViewItem();
            try
            {
                conn.Close();
                conn.Open();
                OleDbDataReader dbReader = cmd1.ExecuteReader();
                while (dbReader.Read())
                {
                    ListViewItem li = new ListViewItem();
                    li.SubItems.Clear();
                    li.SubItems[0].Text = dbReader["customerName"].ToString();
                    li.SubItems.Add("挂号费");
                    li.SubItems.Add("");
                    li.SubItems.Add("");
                    li.SubItems.Add(dbReader["regPrice"].ToString() + "元");
                    li.SubItems.Add(dbReader["name"].ToString());
                    li.SubItems.Add(dbReader["regDate"].ToString().Substring(0,9));
                    li.SubItems.Add("");
                    li.SubItems.Add("");
                    li.SubItems.Add("");
                    li.SubItems.Add("");
                    li.SubItems.Add("");
                    li.SubItems.Add("");
                    li.SubItems.Add("");
                    totalPrice += double.Parse(dbReader["regPrice"].ToString());
                    lv.Items.Add(li);
                }
                li2.SubItems.Clear();
                li2.SubItems[0].Text = "挂号费合计";
                li2.SubItems.Add("");
                li2.SubItems.Add("");
                li2.SubItems.Add("");
                li2.SubItems.Add(totalPrice.ToString() + "元");
                li2.SubItems.Add("");
                li2.SubItems.Add("");
                li2.SubItems.Add("");
                li2.SubItems.Add("");
                li2.SubItems.Add("");
                li2.SubItems.Add("");
                li2.SubItems.Add("");
                li2.SubItems.Add("");
                li2.SubItems.Add("");
                lv.Items.Add(li2);
                conn.Close();
            }
            catch (OleDbException ex)
            {
                string s = ex.ToString();
            }
            


        }
    }
}
